﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace EasyInvoice.SqlCEDAL
{
    public class Clients:IClients
    {
       
        public EasyInvoice.Model.Client GetById(long id)
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {

                    sqlConnection.Open();

                    var p = new DynamicParameters();
                    p.Add("@Id", id, System.Data.DbType.Int32, direction: ParameterDirection.Input);

                    string sql = string.Format("SELECT * FROM [Clients] WHERE [Id] = @Id");

                    IEnumerable<EasyInvoice.Model.Client> clients = sqlConnection.Query<EasyInvoice.Model.Client>(sql, p, commandType: CommandType.Text);
                    sqlConnection.Close();
                    if (clients == null)
                    {
                        return null;
                    }
                    else
                    {
                        EasyInvoice.Model.Client returnClient = clients.FirstOrDefault<EasyInvoice.Model.Client>();
                        return returnClient;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public IEnumerable<EasyInvoice.Model.Client> GetAll()
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {

                    sqlConnection.Open();

                    string sql = string.Format("SELECT * FROM [Clients]");

                    IEnumerable<EasyInvoice.Model.Client> clients = sqlConnection.Query<EasyInvoice.Model.Client>(sql, commandTimeout: 0, commandType: CommandType.Text);

                    sqlConnection.Close();

                    return clients;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public long Save(EasyInvoice.Model.Client client) 
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {
                    sqlConnection.Open();

                    var parameters = new DynamicParameters();
                    parameters.Add("@Id", client.Id, System.Data.DbType.Int32, direction: ParameterDirection.Input);
                    parameters.Add("@Name", client.Name, System.Data.DbType.String, direction: ParameterDirection.Input);
                    parameters.Add("@Address", client.Address, System.Data.DbType.String, direction: ParameterDirection.Input);
                    parameters.Add("@PostalCode", client.PostalCode, System.Data.DbType.String, direction: ParameterDirection.Input);
                    parameters.Add("@City", client.City, System.Data.DbType.String, direction: ParameterDirection.Input);
                    parameters.Add("@State", client.State, System.Data.DbType.String, direction: ParameterDirection.Input);
                    parameters.Add("@Country", client.Country, System.Data.DbType.String, direction: ParameterDirection.Input);
                    parameters.Add("@Phone", client.Phone, System.Data.DbType.String, direction: ParameterDirection.Input);
                    parameters.Add("@Email", client.Email, System.Data.DbType.String, direction: ParameterDirection.Input);
                    parameters.Add("@IsMaster", client.IsMaster, System.Data.DbType.Boolean, direction: ParameterDirection.Input);

                    string sql = string.Empty;
                    if (client.Id == 0)
                        sql = string.Format("INSERT INTO [Clients] ( [Name], [Address], [PostalCode], [City], [State], [Country], [Phone], [Email], [IsMaster]) VALUES(@Name, @Address, @PostalCode, @City, @State, @Country, @Phone, @Email, @IsMaster);");
                    else
                        sql = string.Format("UPDATE [Clients] SET [Name] = @Name, [Address] = @Address, [PostalCode] = @PostalCode, [City] = @City, [State] = @State, [Country] = @Country, [Phone] = @Phone, [Email] = @Email, [IsMaster] = @IsMaster WHERE [Id] = @Id");

                    sqlConnection.Execute(sql, parameters, null, commandTimeout: 0, commandType: CommandType.Text);
                    if (client.Id == 0)
                    {
                        sql = "SELECT * FROM [Clients] WHERE [Id] = @@IDENTITY";
                        IEnumerable<EasyInvoice.Model.Client> clients = sqlConnection.Query<EasyInvoice.Model.Client>(sql, commandType: CommandType.Text);
                        client.Id = clients.First().Id;
                    }

                    if (client.Signature != null)
                    {
                        //BLOB INSERTS FAILED WITH SQL Server Compact
                        sql = "UPDATE [Clients] SET [Signature] = @Signature WHERE [Id] = @Id";
                        System.Data.SqlServerCe.SqlCeCommand sqlCommand = sqlConnection.CreateCommand();
                        sqlCommand.CommandText = sql;

                        System.Data.SqlServerCe.SqlCeParameter parameterId = sqlCommand.Parameters.Add("Id", SqlDbType.Int);
                        parameterId.Value = client.Id;

                        System.Data.SqlServerCe.SqlCeParameter parameterImage = sqlCommand.Parameters.Add("Signature", SqlDbType.Image);
                        parameterImage.Value = client.Signature;
                        sqlCommand.ExecuteNonQuery();
                    }

                    sqlConnection.Close();

                    return client.Id;
                }
            }
            catch (Exception)
            {
                throw;
            }   
        }

        public EasyInvoice.Model.Client Delete(EasyInvoice.Model.Client client) 
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {
                    sqlConnection.Open();
                    var p = new DynamicParameters();
                    p.Add("@Id", client.Id, System.Data.DbType.Int32, direction: ParameterDirection.Input);

                    string sql = string.Format("SELECT * FROM [Invoices] WHERE ([ClientId] = @Id) OR ([SellerId] = @Id)");

                    IEnumerable<EasyInvoice.Model.Invoice> invoices = sqlConnection.Query<EasyInvoice.Model.Invoice>(sql, p, commandType: CommandType.Text);
                    if (invoices.ToList().Count != 0)
                        throw new Exception("Client has invoices attached to it. Please first delete the invoices.");

                    sql = string.Format("DELETE FROM [Clients] WHERE [Id] = @Id");
                    sqlConnection.Execute(sql, p, commandType: CommandType.Text);

                    sqlConnection.Close();
                    
                    return client;
                }
            }
            catch (Exception)
            {
                throw;
            }
           
        }
    }
}
